/**
 * RideDatabase.java
 * Provides access to the Ride database.
 * 
 * @author Ryan Senkbeil
 */

package com.msoe.core.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.msoe.core.ride.Location;
import com.msoe.core.ride.Ride;
import com.msoe.core.users.User;

public class RideDatabase 
{
	private static RideDatabase ride;
	private Connection connection;
	
	private RideDatabase()
	{
		// Get a connection to the database
		this.connection = Database.getInstance().getConenction();
	}
	
	/**
	 * Returns the instance of the RideDatabase class so it can be worked with.
	 * @return An instance of the RideDatabase class.
	 */
	public static RideDatabase getInstance()
	{
		if(ride == null) ride = new RideDatabase();
		
		return ride;
	}
	
	private int getNextRideID()
	{
		int retValue = -1;
		
		String sql = "SELECT max(ride_id) from RIDE;";
		
		try
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			ResultSet results = prep.executeQuery();
			
			if(results.next()) retValue = results.getInt(1);
		}
		catch(SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue + 1;
	}
	
	public boolean addRide(Ride toAdd)
	{
		
		boolean retValue = false;
		
		String sql = "INSERT into RIDE values (?,?,?,?,?,?,?,?) ";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, getNextRideID());
			prep.setInt(2, toAdd.getDriver());
			prep.setLong(3, toAdd.getDeparture());
			prep.setString(4, toAdd.getSource().getLatitude());
			prep.setString(5, toAdd.getSource().getLongitude());
			prep.setString(6, toAdd.getDestination().getLatitude());
			prep.setString(7, toAdd.getDestination().getLongitude());
			prep.setInt(8, toAdd.getNumberOfSeats());
			
			prep.executeUpdate();
			retValue = true;
			
			this.connection.commit();
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	public boolean removeRide(int rideID)
	{
		boolean retValue = false;
		
		String sql = "DELETE FROM RIDE WHERE ride_id=?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, rideID);
			
			prep.executeUpdate();
			retValue = true;
			
			this.connection.commit();
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	public boolean addPassenger(Ride ride, User passenger)
	{
		boolean retValue = false;
		
		String sql = "INSERT into PASSENGER values (?,?) ";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			prep.setInt(1, ride.getRideID());
			prep.setInt(2, passenger.getUserID());
			
			prep.executeUpdate();
			retValue = true;
			
			this.connection.commit();
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	public List<Ride> getRides()
	{
		ArrayList<Ride> retValue = new ArrayList<Ride>();
		
		String sql = "select p.passenger, r.* from ride r, passenger p where r.ride_id=p.ride_id";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			ResultSet results = prep.executeQuery(sql);

			while(results.next())
			{
				Ride ride = new Ride();

				ride.setRideID(results.getInt("ride_id"));
				ride.setDeparture(results.getInt("departure_date"));
				ride.setDestination(new Location(results.getString("dest_lat"), results.getString("dest_lon")));
				ride.setSource(new Location(results.getString("src_lat"), results.getString("src_lon")));
				ride.setDriver(UserDatabase.getInstance().getUser(results.getInt("driver")).getUserID());
				ride.setNumberOfSeats(results.getInt("num_seats"));
				ride.addPassenger(UserDatabase.getInstance().getUser(results.getInt("passenger")).getUserID());
				
				retValue.add(ride);
			}
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	public List<Ride> getRides(Date date)
	{
		ArrayList<Ride> retValue = new ArrayList<Ride>();
		
		String sql = "select p.passenger, r.* from ride r, passenger p where r.ride_id=p.ride_id and departure_datetime > ? and departure_datetime < ?";
		
		try 
		{
			PreparedStatement prep = connection.prepareStatement(sql);
			
			// Create the minimum time possible for the date
			Calendar cal = Calendar.getInstance();
			cal.setTimeInMillis(date.getTime());
			cal.set(Calendar.HOUR_OF_DAY, 0);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			cal.set(Calendar.MILLISECOND, 0);
			
			prep.setLong(1, cal.getTimeInMillis());
			
			// Set the maximum time for the date
			cal.set(Calendar.HOUR_OF_DAY, 23);
			cal.set(Calendar.MINUTE, 59);
			cal.set(Calendar.SECOND, 59);
			
			prep.setLong(2, cal.getTimeInMillis());
			
			ResultSet results = prep.executeQuery(sql);

			while(results.next())
			{
				Ride ride = new Ride();

				ride.setRideID(results.getInt("ride_id"));
				ride.setDeparture(results.getLong("departure_date"));
				ride.setDestination(new Location(results.getString("dest_lat"), results.getString("dest_lon")));
				ride.setSource(new Location(results.getString("src_lat"), results.getString("src_lon")));
				ride.setDriver(UserDatabase.getInstance().getUser(results.getInt("driver")).getUserID());
				ride.setNumberOfSeats(results.getInt("num_seats"));
				ride.addPassenger(UserDatabase.getInstance().getUser(results.getInt("passenger")).getUserID());
				
				retValue.add(ride);
			}
		} 
		catch (SQLException e)
		{
			throw new DatabaseException(e);
		}
		
		return retValue;
	}
	
	/**
	 * Gets rides that the user is either giving or receiving.
	 * @param user - The user to get the rides for.
	 * @param driver - If true, will get rides the user is giving. If false, will get rides the user is receiving.
	 * @return A list of rides.
	 */
	public List<Ride> getRides(User user, boolean driver)
	{
		return null;
	}
}
